# -*- coding: utf-8 -*-
"""
Created on Tue Apr 14 14:42:38 2020

@author: Administrator
"""
import urllib
import urllib.request
import re
from bs4 import BeautifulSoup
import time
import random
import requests
import json
import re
import xlrd
import time
import datetime
import openpyxl
import pymysql

import importlib,sys
importlib.reload(sys)

def insert_db(data):
    
    db = pymysql.connect('localhost','root','123456','zhongzhi')
    cusor = db.cursor()
    sql = """INSERT INTO TYprice(品名,规格,市场,日期,价格,网站) VALUES (%s,%s,%s,%s,%s,%s)
    """
    try:
        cusor.executemany(sql,data) #sql执行
        db.commit() #提交到数据库
    except Exception as e: #获取报错信息
        print(e)
    db.close()
y = datetime.datetime.now().year
m = datetime.datetime.now().month
d = datetime.datetime.now().day

conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='123456',
        db='zhongzhi',
        charset='utf8'
    )
    # 获取游标
cursor = conn.cursor()
#  创建数据表的sql 语句  并设置name_id 为主键自增长不为空
sql_selectTb = "SELECT MAX(日期) FROM typrice"

# 在 execute里面执行SQL语句
cursor.execute(sql_selectTb)  
data = cursor.fetchone()  
print(cursor.rowcount)
conn.commit() 
a1 = str(data).split('-')
day1 = (datetime.datetime(y,m,d) - datetime.datetime(int(re.sub("\(\'","",a1[0])),int(a1[1]),int(re.sub("\'\,\)","",a1[2])))).days
        
def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
    workbook.save(path)
    print("xlsx格式表格写入数据成功！")
    
    
url = 'https://www.zyctd.com/Breeds/GetPriceChart'

headers = {
    "Host": "www.zyctd.com",
    "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:54.0) Gecko/20100101 Firefox/54.0",
    "Accept": "*/*",
    "Accept-Language": "zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3",
    "Accept-Encoding": "gzip, deflate, br",
    "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
    "X-Requested-With": "XMLHttpRequest",
    "Referer": "https://www.zyctd.com/jiage/xq70.html",
    "Content-Length": "35",
    "Cookie": "FromsAuthByDbCookie_zytd_Edwin.PrvGuest=1c1uuuuuuuuuuaIab597WcT17c4818Vbc1SaSc1U5acUWcbU4WS0o5qqnc78ca99o698qac9nadmoll5moad015; UM_distinctid=171722e10003d-0a2865dac79651-17397540-15f900-171722e1001341; CNZZDATA1261355531=1147626159-1586755002-%7C1586851405; Hm_lvt_ba57c22d7489f31017e84ef9304f89ec=1586758554,1586830545,1586852111; Hm_lpvt_ba57c22d7489f31017e84ef9304f89ec=1586852111",
    "Connection": "keep-alive"
}

dict1 = xlrd.open_workbook(r'E:\数据\药材网\链接\每日链接.xlsx')#载入字典
table1 = dict1.sheet_by_name('Sheet1')
row2 = table1.nrows 
list1 = []
for j in range(1,237):
    print("获取页数:",j)
    stop = random.uniform(1, 3)
    values = table1.row_values(j)
    data = {"PriceType":"day","mid":values[12]}
    #data = {"PriceType":"day","mid":"14509990006880300"}
    response = requests.post(url=url, data=data, headers=headers, timeout=10)
    #print(response.text)   # 返回值：<Response [200]>
    content0 = json.loads(response.text)
    content = content0['Data']['PriceChartData']
    it = re.findall('\\[(.*?),(.*?)\\]',content)

    try:
        for i in range(len(it)-1,len(it)-1-day1,-1):
            list2 = []
            list2.append(values[10])
            list2.append(values[11])
            list2.append(re.sub('药市','',values[9]))
            if i == 0:
                a1 = it[i][0].replace(it[0][0][0],'')
            else:
                a1 = it[i][0]
            timeStamp = int(a1)/1000
            timeArray = time.localtime(timeStamp)
            otherStyleTime = time.strftime("%Y-%m-%d", timeArray)
            list2.append(otherStyleTime)
            list2.append(it[i][1])
            list2.append("天")
            list1.append(list2)
    
    except:
        print("出错",j)





url = 'https://www.yt1998.com/price/historyPriceQ!getHistoryPrice.do'

headers = {
    "Host": "www.yt1998.com",
    "Connection": "keep-alive",
    "Content-Length": "42",
    "Accept": "application/json, text/javascript, */*; q=0.01",
    "Origin": "https://www.yt1998.com",
    "X-Requested-With": "XMLHttpRequest",
    "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36",
    "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
    "Referer": "https://www.yt1998.com/priceHistory.html?keywords=%E7%9F%B3%E5%88%81%E6%9F%8F&guige=%E7%BB%9F&chandi=%E6%96%B0%E7%96%86&market=1",
    "Accept-Encoding": "gzip, deflate, br",
    "Accept-Language": "zh-CN,zh;q=0.9",
    "Cookie": "JSESSIONID=ADC6AAA4C1429B382CCA1BAABB8E688D; Hm_lvt_21f2fde8228a3428719fdc5669ab5410=1586739170,1586831855,1587015027,1588055351; Hm_lpvt_21f2fde8228a3428719fdc5669ab5410=1588055496"

}
for j in range(1,row2):
    print("获取页数:",j)
    stop = random.uniform(1, 3)
    values = table1.row_values(j)
    url = values[7]
    data = {"ycnam": values[2],"guige": values[3],"chandi": values[4],"market": values[5]}
    #data = {"PriceType":"day","mid":"14509990006880300"}
    response = requests.post(url=url, data=data, headers=headers, timeout=10)
    #print(response.text)   # 返回值：<Response [200]>
    content0 = json.loads(response.text)
    content = content0['data']
    try:
        for i in range(len(content)-1,len(content)-1-day1,-1):
            list2 = []
            list2.append(values[2])
            list2.append(values[3]+values[4])
            list2.append(re.sub('市场','',values[5]))
            list2.append(content[i]['Date_time'])
            list2.append(content[i]['DayCapilization'])
            list2.append("通")
            list1.append(list2)
    except:
        print("出错",j)
            
        
book_name_xlsx = r'E:\数据\药材网\日\天地药通历史每日数据.xlsx'
#book_name_xlsx = input('输出路径：')
sheet_name_xlsx = 'Sheet1'
 
value3 = list1
print('写入中')
#write_excel_xlsx(book_name_xlsx, sheet_name_xlsx, value3)   
print("开始导入")

insert_db(value3)
     